const { execSQL } = require("../db/mysql");
const { hotelNumChangeSQL, nightnumSQL, occupancySQL, rentalSQL } = require("../db/sqlStatement");
const { timesPeriod, sundayFromdate } = require("./controlDB3");

async function rent_hotel_data() {//合同期内酒店数据
    var date = new Date();
    date = date.getFullYear() + '-' + (date.getMonth() + 1) + '-' + date.getDate();
    var sql = `SELECT COUNT(NAME) num FROM hotel_room INNER JOIN (
        SELECT hotelId FROM hotel_rent WHERE gmtCreate<'${date}' AND rentDeadline>'${date}'
    ) hr ON hotel_room.hotelId=hr.hotelId`; //合同内期酒店房间数
    var renthotelroomnum = await execSQL(sql);
    sql = `SELECT COUNT(*) num FROM hotel_rent WHERE gmtCreate<'${date}' AND rentDeadline>'${date}'`;//合同内酒店数
    var renthotelnum = await execSQL(sql);
    sql = `SELECT COUNT(*) num FROM hotel`;//总酒店数
    var hotelnum = await execSQL(sql);
    var arr = [];//存储24周酒店数量变化，从近到远
    var index = 24;
    for (var i = 0; i < 24; i++) {
        date = weeks7(date[0]);//明天看修改后的效果,sql 很可能要修改，会出问题（sql=func(date);）
        sql = hotelNumChangeSQL(date);
        if (i != 23) {
            execSQL(sql).then(data => {
                arr.push({ "week": index--, "weekIncrease": data[0].num })
            });
        } else {
            return execSQL(sql).then(data => {
                arr.push({ "week": 1, "weekIncrease": data[0].num });
                return {
                    'hotelNum': hotelnum[0].num,
                    'rentHotelNum': renthotelnum[0].num,
                    'rentHotelRoomNum': renthotelroomnum[0].num,
                    'hotelNumChange': arr
                };
            });
        }
    }
}
// rent_hotel_data();
async function occupancy_rate() {//总入住率
    var date = ['hr.gmtCreate', 'hr.rentdeadline'];
    var sql1 = nightnumSQL(date)//合同内总夜间数
    var nightnumAll = await execSQL(sql1);
    var sql2 = occupancySQL(date);//总入住数，时间都是合同期内，不会产生歧义
    var occupancyAll = await execSQL(sql2).then(data => {
        var occupancy = 0;
        for (var i = 0; i < data.length; i++) {
            occupancy += parseInt(data[i].desc.substring(data[i].desc.indexOf("（共：") + 3, data[i].desc.indexOf("间）")));
        }
        // console.log(occupancy)
        return occupancy;
    });
    var arr = [], index = 24;
    for (var i = 0; i < 24; i++) {
        date = weeks7(date[0]);
        sql1 = nightnumSQL(date);
        var weeknightnum = await execSQL(sql1);
        sql2 = occupancySQL(date);
        if (i != 23) {
            execSQL(sql2).then(data => {
                var occupancy = 0;
                for (var i = 0; i < data.length; i++) {
                    occupancy += parseInt(data[i].desc.substring(data[i].desc.indexOf("（共：") + 3, data[i].desc.indexOf("间）")));
                }
                arr.push({ 'week': index--, 'livedRate': (occupancy / weeknightnum[0].num) || 0 });
            });
        } else {
            return execSQL(sql2).then(data => {
                var occupancy = 0;
                for (var i = 0; i < data.length; i++) {
                    occupancy += parseInt(data[i].desc.substring(data[i].desc.indexOf("（共：") + 3, data[i].desc.indexOf("间）")));
                }
                arr.push({ 'week': 1, 'livedRate': (occupancy / weeknightnum[0].num) || 0 });
                return { 'totalRoomNumber': nightnumAll[0].num, 'totalLivedRoomNumber': occupancyAll, 'hotelnumChange': arr };
            });
        }
    }
}
// occupancy_rate();
function rental_dealing(hotellist) {//总租金收取情况
    // var date=['hr.gmtCreate','hr.rentDeadline'];//合同期之前就有数据，导致数据不一致
    // var sql=rentalSQL(date);//所有租金信息
    var sql = `SELECT hds.isSettled,hds.amount FROM hotel_daily_settlement hds`
    if (hotellist != undefined) sql += ` where hds.hotelId in (${hotellist})`;
    var prepaidRentalAll = 0, unpaidRentalAll = 0;
    var date = new Date();//处理24周租金数据
    date = new Date(date.setDate(date.getDate() - 168));
    date = date.getFullYear() + '-' + (date.getMonth() + 1) + '-' + date.getDate();
    var sql2 = `SELECT hro.gmtCreate,hro.payAmountCent,hro.payTime,hro.amountCent FROM hotel_rent_order hro WHERE hro.gmtCreate>'${date}'`
    if (hotellist != undefined) sql2 += ` and hro.hotelId in (${hotellist})`;
    return Promise.all([execSQL(sql), execSQL(sql2)]).then(([data, data2]) => {
        for (var i = 0; i < data.length; i++) {
            if (data[i].isSettled == 1) prepaidRentalAll += data[i].amount;
            else unpaidRentalAll += data[i].amount;
        }
        var arr = {};
        for (var i = 0; i < data2.length; i++) {
            var createTime = data2[i].gmtCreate.getFullYear() + '-' + (data2[i].gmtCreate.getMonth() + 1) + '-' + data2[i].gmtCreate.getDate();
            if (arr[createTime]) {
                arr[createTime].totalRentInWeek += (data2[i].amountCent / 100);
                arr[createTime].prepaidRental += (data2[i].payAmountCent / 100);
            } else arr[createTime] = {
                'week': createTime, 'totalRentInWeek': data2[i].amountCent / 100,
                'prepaidRental': data2[i].payAmountCent / 100
            }
        }
        return arr;
    }).then(arr => {
        var arrs = [];
        for (var i in arr) arrs.push(arr[i])
        return {
            'rentalAll': prepaidRentalAll + unpaidRentalAll,
            'prepaidRentalAll': prepaidRentalAll,
            'unpaidRentalAll': unpaidRentalAll,
            'rentChangeInWeek': arrs
        }
    })
}
// rental_dealing('2,3').then(data=>console.log(data));
//待优化，上面两函数用下面一函数实现部分功能
async function ranking_list() {//获取各项需排序数据
    var hotelDate = await hotelMessage();//酒店id、名字、房间数
    var sql1 = `SELECT hds.hotelId,COUNT(*) night FROM hotel_daily_settlement hds INNER JOIN hotel_rent hr
        ON hds.hotelId=hr.hotelId AND hr.gmtCreate<hds.from AND hr.rentDeadline>hds.from GROUP BY hotelId`;//每个酒店夜间数
    var hotelNightData = await execSQL(sql1).then(data => {//将夜间天数加入酒店信息中
        for (var i = 0; i < data.length; i++) {
            hotelDate[data[i].hotelId]['totalNumRight'] = data[i].night * hotelDate[data[i].hotelId].roomnum;
            // hotelDate[]['night']=data[i].night;//可去掉，夜数
        }
        // console.log(hotelDate)
        return hotelDate;
    });//合同内天数
    // console.log(hotelNightData)
    var sql2 = `SELECT hds.hotelId,hds.desc,isSettled FROM hotel_daily_settlement hds INNER JOIN
        hotel_rent hr ON hds.hotelId=hr.hotelId AND hr.gmtCreate<hds.from AND hr.rentDeadline>hds.from ORDER BY hds.hotelId`;//获取desc信息
    return execSQL(sql2).then(data => {//同时处理各酒店入住率和租金收取情况
        for (var i = 0; i < data.length; i++) {
            if (hotelNightData[data[i].hotelId]['lived']) hotelNightData[data[i].hotelId]['lived'] += parseInt(data[i].desc.substring(data[i].desc.indexOf("（共：") + 3, data[i].desc.indexOf("间）")));
            else hotelNightData[data[i].hotelId]['lived'] = parseInt(data[i].desc.substring(data[i].desc.indexOf("（共：") + 3, data[i].desc.indexOf("间）")));
            //计算收租
            var rental = parseInt(data[i].desc.substring(data[i].desc.indexOf("共计：") + 3, data[i].desc.indexOf("元。")))
            if (hotelNightData[data[i].hotelId]['rentalAll']) {
                hotelNightData[data[i].hotelId].rentalAll += rental;
                if (data[i].isSettled == 1) hotelNightData[data[i].hotelId].prepaidRental += rental;
            } else {
                hotelNightData[data[i].hotelId].rentalAll = rental;
                if (data[i].isSettled == 1) hotelNightData[data[i].hotelId]['prepaidRental'] = rental;
                else hotelNightData[data[i].hotelId]['prepaidRental'] = 0;
            }
        }
        // console.log('arr',hotelNightData)
        return hotelNightData;
    }).then(data => {//计算入住率
        // var roomNumAll=0,totalNumRightAll=0,livedAll=0,totalRentalAll=0,prepaidRentalAll=0;
        var arrs = [];
        for (var key in data) {
            arrs.push({
                'name': data[key].name,
                'livedRate': data[key].lived / data[key].totalNumRight,
                "rentalAll": data[key].rentalAll,
                "prepaidRental": data[key].prepaidRental,
                "roomnum": data[key].roomnum,
            });
            // roomNumAll+=data[key].roomnum;//所有酒店数据合并
            // totalNumRightAll+=data[key].totalNumRight;
            // livedAll+=data[key].lived;
            // totalRentalAll+=data[key].rentalAll;
            // prepaidRentalAll+=data[key].prepaidRental;
        }
        // data['roomNumAll']=roomNumAll;
        // data['totalNumRightAll']=totalNumRightAll;
        // data['livedAll']=livedAll;
        // data['totalRentalAll']=totalRentalAll;
        // data['prepaidRentalAll']=prepaidRentalAll;
        // data['livedRate']=livedAll/totalNumRightAll;
        // console.log(data)
        return arrs;
    });
}
function hotelMessage() {//酒店id、名字、房间数
    var sql = `SELECT hr.hotelId,hro.name,roomnum FROM hotel_rent hr INNER JOIN (
        SELECT hr.hotelId,hotel.name,COUNT(*) roomnum FROM hotel INNER JOIN hotel_room hr ON hotel.id=hr.hotelId GROUP BY hr.hotelId 
        ) hro ON hr.hotelId=hro.hotelId ORDER BY roomnum desc`;
    return execSQL(sql).then(data => {
        var arrs = {};
        for (var i = 0; i < data.length; i++) {
            arrs[data[i].hotelId] = { 'name': data[i].name, 'roomnum': data[i].roomnum }
        }
        return arrs;
    })
}
//优化：建立一个全局变量，含每个酒店id、名字、房间数
// ranking_list();
async function occupancy_rental(left, right) {//入住率 和收租
    var sql = `SELECT hds.hotelId,hds.from,hds.desc,hds.amount,hds.isSettled FROM hotel_daily_settlement hds WHERE hds.from>'${left}' AND hds.from<'${right}' ORDER BY hotelId`;
    var hotelData = await hotelMessage();//酒店id、名字、房间数
    // var installData=await install_room(left,right);//新安装房间数
    return execSQL(sql).then(data => {
        // console.log(installData);return;
        var arrs = {};
        var timePeriod = timesPeriod(left, right);//判断时间单位shi月、周、日
        for (var i = 0; i < data.length; i++) {
            var time = data[i].from.getFullYear() + '-' + (data[i].from.getMonth() + 1) + '-' + data[i].from.getDate();//用于统计安装房间
            if (timePeriod == '月') var fromtime = data[i].from.getFullYear() + '-' + (data[i].from.getMonth() + 1);
            else if (timePeriod == '周') var fromtime = sundayFromdate(data[i].from);//将日期转换成本周周日或一
            else var fromtime = time;
            if (arrs[fromtime]) {
                arrs[fromtime].lived += parseInt(data[i].desc.substring(data[i].desc.indexOf("（共：") + 3, data[i].desc.indexOf("间）")));
                arrs[fromtime].roomnum += hotelData[data[i].hotelId].roomnum;
                // arrs[fromtime].installnum+=installData[time];

            } else arrs[fromtime] = {
                // 'installnum':installData[time]||0,//安装房间数据要修改
                'hotelId': data[i].hotelId,
                'name': hotelData[data[i].hotelId].name,
                'from': fromtime,
                'lived': parseInt(data[i].desc.substring(data[i].desc.indexOf("（共：") + 3, data[i].desc.indexOf("间）"))),
                'roomnum': hotelData[data[i].hotelId].roomnum
            }
        }
        return arrs;
    }).then(data => {
        var arrs = [];
        for (var key in data) arrs.push(data[key]);
        return arrs;
    })
}
// occupancy_rental('2022-7-1','2022-7-31').then(data=>console.log(data));
function occupancy_rental2(leftTime, rightTime) {//入住和租金部分，租金收取情况
    if (!leftTime) { return "时间参数为空"; }
    var sql = `SELECT gmtCreate,payAmountCent,payTime,amountCent FROM hotel_rent_order hro WHERE gmtCreate>'${leftTime}' AND gmtCreate<'${rightTime}'`;
    var timePeriod = timesPeriod(leftTime, rightTime);//判断时间单位shi月、周、日
    return execSQL(sql).then(data => {
        var arrs = {};
        for (var i = 0; i < data.length; i++) {
            if (timePeriod == '月') var fromtime = data[i].gmtCreate.getFullYear() + '-' + (data[i].gmtCreate.getMonth() + 1);
            else var fromtime = data[i].gmtCreate.getFullYear() + '-' + (data[i].gmtCreate.getMonth() + 1) + '-' + data[i].gmtCreate.getDate();
            if (arrs[fromtime]) {
                arrs[fromtime].rental += (data[i].amountCent / 100);
                arrs[fromtime].payAmountCent += (data[i].payAmountCent / 100);
            } else arrs[fromtime] = {
                'rental': data[i].amountCent / 100,
                'gmtCreate': fromtime,
                'payAmountCent': data[i].payAmountCent / 100,
                // 'payTime':data[i].payTime.getFullYear()+'-'+(data[i].payTime.getMonth()+1)+'-'+data[i].payTime.getDate()
            };
        }
        return arrs;
    }).then(data => {
        var arrs = [];
        for (var key in data) arrs.push(data[key]);
        return arrs;
    });
}
// occupancy_rental2();
function install_room(leftTime, rightTime) {//存储新安装房间数量变化，从近到远
    var sql = `SELECT hrr.from,COUNT(*) installnum FROM hotel_rent_room hrr WHERE hrr.from>'${leftTime}' AND hrr.from<'${rightTime}' GROUP BY hrr.from  ORDER BY hrr.from`;
    return execSQL(sql).then(data => {//将某些日子安装为0 的数据加上
        var arrs = {}, index = 0;
        var left = new Date(leftTime);
        var right = new Date(rightTime);
        while (left < right) {
            left = new Date(left);
            left.setDate(left.getDate() + 1);
            if (index == data.length || data[index].from > left) {
                arrs[left.getFullYear() + '-' + (left.getMonth() + 1) + '-' + left.getDate()] = 0;
            } else {
                arrs[left.getFullYear() + '-' + (left.getMonth() + 1) + '-' + left.getDate()] = data[index++].installnum;
            }
        }
        return arrs;
    }).then(data => {
        var arrs = {}
        var timePeriod = timesPeriod(leftTime, rightTime);//判断时间单位shi月、周、日
        for (var key in data) {
            if (timePeriod == '月') var fromtime = key.substring(0, key.lastIndexOf('-'));
            else if (timePeriod == '周') var fromtime = sundayFromdate(key);//将日期转换成本周周日或一
            else var fromtime = key;
            if (arrs[fromtime]) arrs[fromtime].installnum += data[key];
            else arrs[fromtime] = { 'from': fromtime, 'installnum': data[key] || 0 }
        }
        return arrs;
    }).then(data => {
        var arrs = [];
        for (var key in data) arrs.push(data[key]);
        return arrs;
    });
}
// install_room('2022-7-1','2022-7-31').then(data=>console.log(data))
function weeks7(right) {//获取本周周日日期或上周周日日期
    if (right.indexOf('23:59:59') < 0) {
        var date = new Date();
        switch (date.getDay()) {
            case 1: date = new Date(date.setDate(date.getDate() + 6));
                break;
            case 2: date = new Date(date.setDate(date.getDate() + 5));
                break;
            case 3: Date(date.setDate(date.getDate() + 4));
                break;
            case 4: date = new Date(date.setDate(date.getDate() + 3));
                break;
            case 5: date = new Date(date.setDate(date.getDate() + 2));
                break;
            case 6: date = new Date(date.setDate(date.getDate() + 1));
                break;
        }
        right = date.getFullYear() + '-' + (date.getMonth() + 1) + '-' + date.getDate() + ' 23:59:59';
    } else right = right.substring(1, right.length - 1);
    var left = new Date(right);
    left = new Date(left.setDate(left.getDate() - 7));
    left = '\'' + left.getFullYear() + '-' + (left.getMonth() + 1) + '-' + left.getDate() + ' 23:59:59' + '\'';
    right = '\'' + right + '\'';
    return [left, right];
}
function time_transition(time) {//将类如今天转换从具体时间段
    var right = new Date();
    var left = new Date();
    if (time === '今年') {
        left.setMonth(0); left.setDate(0);//去年最后一天
        right = right.getFullYear() + '-' + (right.getMonth() + 1) + '-' + right.getDate() + ' 23:59:59';
        left = left.getFullYear() + '-' + (left.getMonth() + 1) + '-' + left.getDate() + ' 23:59:59';

    } else if (time === '去年') {//去年，前年，没有数据
        right.setMonth(0); right.setDate(0);//去年最后一天
        left = new Date(right); left.setMonth(0); left.setDate(0);//前年最后一天
        right = right.getFullYear() + '-' + (right.getMonth() + 1) + '-' + right.getDate() + ' 23:59:59';
        left = left.getFullYear() + '-' + (left.getMonth() + 1) + '-' + left.getDate() + ' 23:59:59';
    } else if (time.length > 4) {
        var date = time.split(',');
        left = date[0]; right = date[1];

    } else {
        if (time === '今天' || time === '今日') {
            right = right.getFullYear() + '-' + (right.getMonth() + 1) + '-' + right.getDate() + ' 23:59:59';
            left.setDate(left.getDate() - 1);
            left = left.getFullYear() + '-' + (left.getMonth() + 1) + '-' + left.getDate() + ' 23:59:59';
        } else if (time === '昨日' || time === '昨天') {//今天向前推7天,变成推前8天
            right.setDate(right.getDate() - 1);
            right = right.getFullYear() + '-' + (right.getMonth() + 1) + '-' + right.getDate() + ' 23:59:59';
            left = new Date(left.setDate(left.getDate() - 2));
            left = left.getFullYear() + '-' + (left.getMonth() + 1) + '-' + left.getDate() + ' 23:59:59';
            // console.log("昨天",left,right);

        } else if (time === '本周') {
            var date = weeks7("");//本周时间
            left = date[0].substring(1, date[0].length - 1); right = date[1].substring(1, date[1].length - 1);
        } else if (time === '上周') {
            var date = weeks7(weeks7("")[0]);//上周时间
            left = date[0].substring(1, date[0].length - 1); right = date[1].substring(1, date[1].length - 1);

        } else if (time === '本月') {
            left.setDate(0);//上月最后一天
            right = right.getFullYear() + '-' + (right.getMonth() + 1) + '-' + right.getDate() + ' 23:59:59';
            left = left.getFullYear() + '-' + (left.getMonth() + 1) + '-' + left.getDate() + ' 23:59:59';

        } else {//上月
            right = new Date(right.setDate(0));//上个月最后一天
            left = new Date(right);
            left.setDate(0);//上上个月最后一天
            right = right.getFullYear() + '-' + (right.getMonth() + 1) + '-' + right.getDate() + ' 23:59:59';
            left = left.getFullYear() + '-' + (left.getMonth() + 1) + '-' + left.getDate() + ' 23:59:59';
        }
    }
    return [left, right];
}
module.exports = {
    rent_hotel_data,
    occupancy_rate,
    rental_dealing,
    ranking_list,
    occupancy_rental, occupancy_rental2,
    time_transition, hotelMessage,
    install_room
}